package com.navi.loaddata.loadService.impl.PanelData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.DReloadPnlGls;
import com.navi.loaddata.core.gpEntity.MinMaxEvttimeStamp;
import com.navi.loaddata.core.gpEntity.YearMonth;
import com.navi.loaddata.core.gpEntity.base.ReloadPanel;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.mysqlEntity.EtlConfDDefect;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JacksonUtil;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.loadService.SpringContext;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.postgresql.util.PSQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class LoadPanelDataBean {
    private static final Logger logger = LoggerFactory.getLogger(LoadPanelDataBean.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private MySqlRepositoryImpl dao;

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void deletePeriodData(EtlConfDModel etlConfDModel, String tmpTab, String defectShop, String EvtStartTime, String EvtEndTime) {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlDelDef = new StringBuffer();
        sqlDelDef.append("delete from " + tmpTab);

        //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
        if ("Y".equals(etlConfDModel.getExt1().toString())) {
            sqlDelDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
            sqlDelDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
        } else {
//            sqlDelDef.append(" where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
//            sqlDelDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
//            sqlDelDef.append(" and evt_timestamp >= '" + EvtStartTime + "'");
//            sqlDelDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
            sqlDelDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
            sqlDelDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
        }
        sqlDelDef.append(" and ope_id = '"+ etlConfDModel.getOpe() +"'");

        try {
            SQLQuery DelDef_query = session.createSQLQuery(sqlDelDef.toString());
            logger.info("Delete Data SQL:" + sqlDelDef.toString());

            Date startTime = new Date();

            DelDef_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startTime.getTime())/1000;

            logger.info(defectShop + " "+  tmpTab +" delete from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Delete Data耗时:" + durableTime +"s");
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlDelDef.toString():  **********    " + sqlDelDef.toString());
            e.printStackTrace();
            try {
                throw new ETLException2(ErrorCode.ERROR_DELETE_GP_DEFECT_SUM, e);
            } catch (ETLException2 e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }
    }

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void deleteDuplicateData(EtlConfDModel etlConfDModel, String tmpTab, String panelShop, String EvtStartTime, String EvtEndTime, String errMsg, int errCnt, Boolean loopFlag, String MaxEndTime, List<YearMonth> yearMonthList) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        String startTime = "";

        //获取panel的最小release时间，作为起始时间
        String nowTime = LocalDateTime.now().toString();
        int month = Integer.parseInt(yearMonthList.get(0).getMonth(), 16);
        if (month < 10 ) {
            startTime = nowTime.substring(0, 2) + yearMonthList.get(0).getYear() + "-0" + month +"-01 00:00:00";
        } else {
            startTime = nowTime.substring(0, 2) + yearMonthList.get(0).getYear() + "-" + month +"-01 00:00:00";
        }

        StringBuffer sqlsumDef = new StringBuffer();
        StringBuffer sqlbuffDel = new StringBuffer();

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
            opeList.add("L4110");
            opeList.add("L4212");
            opeList.add("CT1");
            opeList.add("CT1F");
        }

        //一些站点数据只存glass表
        if (opeList.contains(etlConfDModel.getOpe())) {
            //获取源数据汇总defectqty
            sqlsumDef.append(" select distinct tft_glass_id as glass_id, ope_id");
            sqlsumDef.append(" from " + tmpTab.substring(0, 12) + "glass_f");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlsumDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp < '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlsumDef.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlsumDef.append(" and real_flg = 'Y'");
//            sqlsumDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlsumDef.append(" and tft_glass_id in ('"+ panelList + "')");
            }


            //By ope_id、panel_id删除重复数据
            sqlbuffDel.append("delete from " + tmpTab + " a ");
            sqlbuffDel.append(" where (glass_id, ope_id) in ");
            sqlbuffDel.append(" (" + sqlsumDef + ")");
            sqlbuffDel.append(" and a.evt_timestamp >= '" + startTime + "'");


            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + MaxEndTime + "'");
            } else {
                sqlbuffDel.append(" and a.evt_timestamp <= '" + EvtEndTime + "'");
            }
        } else {
            //获取源数据汇总defectqty
            sqlsumDef.append(" select distinct glass_id, ope_id");
            sqlsumDef.append(" from " + tmpTab.substring(0, 12) + "panel_f");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlsumDef.append(" where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp < '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlsumDef.append(" where evt_timestamp >= '" + EvtStartTime + "'");
                sqlsumDef.append(" and evt_timestamp <= '" + EvtEndTime + "'");
                sqlsumDef.append(" and db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlsumDef.append(" and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlsumDef.append(" and ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlsumDef.append(" and real_flg = 'Y'");
//            sqlsumDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlsumDef.append(" and glass_id in ('"+ panelList + "')");
            }

            //By ope_id、panel_id删除重复数据
            sqlbuffDel.append("delete from " + tmpTab + " a ");
            sqlbuffDel.append(" where (glass_id, ope_id) in ");
            sqlbuffDel.append(" (" + sqlsumDef + ")");
            sqlbuffDel.append(" and a.evt_timestamp >= '" + startTime + "'");

            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true) {
                sqlbuffDel.append(" and a.evt_timestamp < '" + MaxEndTime + "'");
            } else {
                sqlbuffDel.append(" and a.evt_timestamp <= '" + EvtEndTime + "'");
            }
        }

        try {
            SQLQuery DeltDef_query = session.createSQLQuery(sqlbuffDel.toString());
            logger.info("Delete duplicate data SQL:" + sqlbuffDel.toString());

            Date startDate = new Date();

            DeltDef_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startDate.getTime())/1000;
            logger.info(panelShop + " "+  tmpTab +" delete duplicate data from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Delete duplicate data耗时:" + durableTime +"s");
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlbuffDel.toString():  **********    " + sqlbuffDel.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }

            errCnt++;

            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            e.printStackTrace();

            throw new RuntimeException(e);
        }
    }

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void insertData(EtlConfDModel etlConfDModel, String tmpTab, String panelShop, String EvtStartTime, String EvtEndTime, String errMsg, int okCnt, Boolean loopFlag) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        StringBuffer sqlBuffDef = new StringBuffer();
        StringBuffer sqlInsrDef = new StringBuffer();

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
            opeList.add("L4110");
            opeList.add("L4212");
            opeList.add("CT1");
            opeList.add("CT1F");
        }

        //一些站点数据只存glass表
        if (opeList.contains(etlConfDModel.getOpe())) {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select glass_id, ope_id, lot_id, evt_timestamp, db_timestamp, ope_mode, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, glass_rej_code, cel_repair_grade,");
            sqlBuffDef.append(" cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id, pnl_rej_type, pnl_judgment_grade, pnl_jge,");
            sqlBuffDef.append(" pnl_grade, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty from (");
            sqlBuffDef.append(" select coalesce(defectHis.tft_glass_id,'-') as glass_id, defectHis.ope_id,");

            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append("  '" + etlConfDModel.getRunStartTimestamp() + "' as evt_timestamp,");
            } else {
                sqlBuffDef.append(" case when cast(substring(evt_timestamp,15,2) as int4) >= 0");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 15");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'00:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 15");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 30");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'15:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 30");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 45");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'30:00' as timestamp)");
                sqlBuffDef.append(" else cast(substring(evt_timestamp,1,14)||'45:00' as timestamp) end evt_timestamp,");
            }
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, coalesce(defectHis.ope_mode,'-') as ope_mode, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.prod_id, defectHis.eqpt_id, defectHis.tft_glass_id, defectHis.ope_id, defectHis.glass_rej_code, defectHis.cel_repair_grade,");
            sqlBuffDef.append(" defectHis.cel_repair_flg, defectHis.glass_grade, sum(defectHis.glass_defect_cnt), defectHis.source_ope_id");
            sqlBuffDef.append(" ) as rowCnt, coalesce(defectHis.operator_id,'-') as operator_id, coalesce(defectHis.glass_rej_code,'-') as glass_rej_code, coalesce(defectHis.cel_repair_grade,'-') as cel_repair_grade,");
            sqlBuffDef.append(" coalesce(defectHis.cel_repair_flg,'-') as cel_repair_flg, coalesce(defectHis.glass_grade,'-') as glass_grade, coalesce(sum(defectHis.glass_defect_cnt),0) as glass_defect_cnt,");
            sqlBuffDef.append(" coalesce(defectHis.source_ope_id,'-') as source_ope_id, '-' as pnl_rej_type, '-' as pnl_judgment_grade, '-'as pnl_jge, '-' as pnl_grade , 0 as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, '-' as ext_6, '-' as ext_7, '-' as ext_8, '-' as ext_9, '-' as ext_10, count(1) as panel_qty");
            //            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "glass_f defectHis");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and defectHis.tft_glass_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by defectHis.tft_glass_id, defectHis.ope_id, defectHis.lot_id, defectHis.ope_mode, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, defectHis.operator_id,");
            sqlBuffDef.append(" defectHis.glass_rej_code, defectHis.cel_repair_grade, defectHis.cel_repair_flg, defectHis.glass_grade,defectHis.source_ope_id");

            if (!"Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(", defectHis.evt_timestamp");
            }

            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (glass_id, ope_id, lot_id, evt_timestamp, db_timestamp, ope_mode,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" glass_rej_code, cel_repair_grade, cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty)" + sqlBuffDef);
        } else if ("OC".equals(etlConfDModel.getShop())) {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select glass_id, ope_id, lot_id, evt_timestamp, db_timestamp, ope_mode, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, glass_rej_code, cel_repair_grade,");
            sqlBuffDef.append(" cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id, pnl_rej_type, pnl_judgment_grade, pnl_jge,");
            sqlBuffDef.append(" pnl_grade, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty from (");
            sqlBuffDef.append(" select coalesce(defectHis.glass_id,'-') as glass_id, defectHis.ope_id,");

            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append("  '" + etlConfDModel.getRunStartTimestamp() + "' as evt_timestamp,");
            } else {
                sqlBuffDef.append(" case when cast(substring(evt_timestamp,15,2) as int4) >= 0");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 15");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'00:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 15");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 30");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'15:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 30");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 45");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'30:00' as timestamp)");
                sqlBuffDef.append(" else cast(substring(evt_timestamp,1,14)||'45:00' as timestamp) end evt_timestamp,");
            }
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, '-' as ope_mode, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.prod_id, defectHis.eqpt_id, defectHis.glass_id, defectHis.ope_id, defectHis.pnl_rej_type, defectHis.pnl_judgment_grade,");
            sqlBuffDef.append(" defectHis.pnl_jge, defectHis.pnl_grade, sum(defectHis.pnl_defect_cnt)) as rowCnt, '-' as operator_id, '-' as glass_rej_code, '-' as cel_repair_grade,");
            sqlBuffDef.append(" '-' as cel_repair_flg, '-' as glass_grade, 0 as glass_defect_cnt, '-' as source_ope_id,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_rej_type,'-') as pnl_rej_type, coalesce(defectHis.pnl_judgment_grade,'-') as pnl_judgment_grade,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_jge,'-') as pnl_jge, coalesce(defectHis.pnl_grade ,'-') as pnl_grade , coalesce(sum(defectHis.pnl_defect_cnt), 0) as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, '-' as ext_6, '-' as ext_7, '-' as ext_8, '-' as ext_9, '-' as ext_10, count(1) as panel_qty");
            //            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "panel_f defectHis");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and defectHis.glass_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.glass_id, defectHis.ope_id, defectHis.lot_id, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner, ");
            sqlBuffDef.append(" defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade, defectHis.pnl_defect_cnt");

            if (!"Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(", defectHis.evt_timestamp");
            }

            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (glass_id, ope_id, lot_id, evt_timestamp, db_timestamp, ope_mode,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" glass_rej_code, cel_repair_grade, cel_repair_flg, glass_grade, glass_defect_cnt, source_ope_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty)" + sqlBuffDef);
        } else if ("CF".equals(etlConfDModel.getShop())) {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select glass_id, ope_id, lot_id, evt_timestamp, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, pnl_rej_type, pnl_judgment_grade,");
            sqlBuffDef.append(" pnl_jge, pnl_grade, pnl_defect_cnt, source_pnl_jge, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty from (");
            sqlBuffDef.append(" select coalesce(defectHis.glass_id,'-') as glass_id, defectHis.ope_id,");

            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append("  '" + etlConfDModel.getRunStartTimestamp() + "' as evt_timestamp,");
            } else {
                sqlBuffDef.append(" case when cast(substring(evt_timestamp,15,2) as int4) >= 0");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 15");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'00:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 15");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 30");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'15:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 30");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 45");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'30:00' as timestamp)");
                sqlBuffDef.append(" else cast(substring(evt_timestamp,1,14)||'45:00' as timestamp) end evt_timestamp,");
            }
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.prod_id, defectHis.eqpt_id, defectHis.glass_id, defectHis.ope_id, defectHis.pnl_rej_type, defectHis.pnl_judgment_grade,");
            sqlBuffDef.append(" defectHis.pnl_jge, defectHis.pnl_grade, sum(defectHis.pnl_defect_cnt)) as rowCnt,");
            sqlBuffDef.append(" '-' as operator_id, coalesce(defectHis.pnl_rej_type,'-') as pnl_rej_type, coalesce(defectHis.pnl_judgment_grade,'-') as pnl_judgment_grade,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_jge,'-') as pnl_jge, coalesce(defectHis.pnl_grade,'-') as pnl_grade, coalesce(sum(defectHis.pnl_defect_cnt),0) as pnl_defect_cnt, coalesce(defectHis.source_pnl_jge,'-') as source_pnl_jge,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, '-' as ext_6, '-' as ext_7, '-' as ext_8, '-' as ext_9, '-' as ext_10, count(1) as panel_qty");
            //            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "panel_f defectHis");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and defectHis.glass_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.glass_id, defectHis.ope_id, defectHis.lot_id, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade, defectHis.source_pnl_jge");

            if (!"Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(", defectHis.evt_timestamp");
            }
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (glass_id, ope_id, lot_id, evt_timestamp, db_timestamp,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt, source_pnl_jge,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty)" + sqlBuffDef);
        } else {
            //获取源数据汇总panel_qty
            sqlBuffDef.append(" select glass_id, ope_id, lot_id, evt_timestamp, db_timestamp, cald_date, shf_key, shf_id,");
            sqlBuffDef.append(" hour_key, shop_id, eqpt_id, prod_id, owner, operator_id, pnl_rej_type, pnl_judgment_grade,");
            sqlBuffDef.append(" pnl_jge, pnl_grade, pnl_defect_cnt, ext_1, ext_2, ext_3, ext_4, ext_5,");
            sqlBuffDef.append(" ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty from (");
            sqlBuffDef.append(" select coalesce(defectHis.glass_id,'-') as glass_id, defectHis.ope_id,");

            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append("  '" + etlConfDModel.getRunStartTimestamp() + "' as evt_timestamp,");
            } else {
                sqlBuffDef.append(" case when cast(substring(evt_timestamp,15,2) as int4) >= 0");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 15");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'00:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 15");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 30");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'15:00' as timestamp)");
                sqlBuffDef.append(" when cast(substring(evt_timestamp,15,2) as int4) >= 30");
                sqlBuffDef.append(" and cast(substring(evt_timestamp,15,2) as int4) < 45");
                sqlBuffDef.append(" then cast(substring(evt_timestamp,1,14)||'30:00' as timestamp)");
                sqlBuffDef.append(" else cast(substring(evt_timestamp,1,14)||'45:00' as timestamp) end evt_timestamp,");
            }
            sqlBuffDef.append(" now() as db_timestamp, defectHis.cald_date, defectHis.shf_key_fk as shf_key, defectHis.shf_id, defectHis.hour_key_fk as hour_key, defectHis.shop_id,");
            sqlBuffDef.append(" defectHis.lot_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" row_number() over(partition by defectHis.prod_id, defectHis.eqpt_id, defectHis.glass_id, defectHis.ope_id, defectHis.pnl_rej_type, defectHis.pnl_judgment_grade,");
            sqlBuffDef.append(" defectHis.pnl_jge, defectHis.pnl_grade, sum(defectHis.pnl_defect_cnt)) as rowCnt,");
            sqlBuffDef.append(" '-' as operator_id, coalesce(defectHis.pnl_rej_type,'-') as pnl_rej_type, coalesce(defectHis.pnl_judgment_grade,'-') as pnl_judgment_grade,");
            sqlBuffDef.append(" coalesce(defectHis.pnl_jge,'-') as pnl_jge, coalesce(defectHis.pnl_grade,'-') as pnl_grade, coalesce(sum(defectHis.pnl_defect_cnt),0) as pnl_defect_cnt,");
            sqlBuffDef.append(" '-' as ext_1, '-' as ext_2, '-' as ext_3, '-' as ext_4, '-' as ext_5, '-' as ext_6, '-' as ext_7, '-' as ext_8, '-' as ext_9, '-' as ext_10, count(1) as panel_qty");
            //            sqlBuffDef.append(" from wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_f defectHis, wpp_" + tmpTab.substring(tmpTab.length() - 11, tmpTab.length() - 4) + "_panel_f panel");
            sqlBuffDef.append(" from " + tmpTab.substring(0, 12) + "panel_f defectHis");

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else if (loopFlag == true){
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp < '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            } else {
                sqlBuffDef.append(" where defectHis.evt_timestamp >= '" + EvtStartTime + "'");
                sqlBuffDef.append(" and defectHis.evt_timestamp <= '" + EvtEndTime + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'");
                sqlBuffDef.append(" and defectHis.db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'");
            }

            sqlBuffDef.append(" and defectHis.ope_id = '" + etlConfDModel.getOpe() + "'");
            sqlBuffDef.append(" and defectHis.real_flg = 'Y'");
//            sqlBuffDef.append(" and defect_code not like '*%'");

            //存在Panel上的Defect数据异常，需要重跑
            if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
                String panelList = String.join("','", etlConfDModel.getPanelList());
                sqlBuffDef.append(" and defectHis.glass_id in ('"+ panelList + "')");
            }

            sqlBuffDef.append(" group by  defectHis.glass_id, defectHis.ope_id, defectHis.lot_id, defectHis.cald_date, defectHis.shf_key_fk, ");
            sqlBuffDef.append(" defectHis.shf_id, defectHis.hour_key_fk, defectHis.shop_id, defectHis.eqpt_id, defectHis.prod_id, defectHis.owner,");
            sqlBuffDef.append(" defectHis.pnl_rej_type, defectHis.pnl_judgment_grade, defectHis.pnl_jge, defectHis.pnl_grade");

            if (!"Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef.append(", defectHis.evt_timestamp");
            }
            sqlBuffDef.append(" ) defectHis");
            sqlBuffDef.append(" where defectHis.rowCnt = 1");

            //插入数据到各站点Defect Summary直表
            sqlInsrDef.append("Insert into " + tmpTab);
            sqlInsrDef.append(" (glass_id, ope_id, lot_id, evt_timestamp, db_timestamp,");
            sqlInsrDef.append(" cald_date, shf_key, shf_id, hour_key, shop_id, eqpt_id, prod_id, owner, operator_id,");
            sqlInsrDef.append(" pnl_rej_type, pnl_judgment_grade, pnl_jge, pnl_grade, pnl_defect_cnt,");
            sqlInsrDef.append(" ext_1, ext_2, ext_3, ext_4, ext_5, ext_6, ext_7, ext_8, ext_9, ext_10, panel_qty)" + sqlBuffDef);
        }

        try {
            SQLQuery InsrDef_query = session.createSQLQuery(sqlInsrDef.toString());
            logger.info("Insert data SQL:" + sqlInsrDef.toString());

            Date startTime = new Date();

            InsrDef_query.executeUpdate();

            Date endTime = new Date();
            long durableTime = (endTime.getTime() - startTime.getTime())/1000;

            logger.info(panelShop + " "+  tmpTab +" insert data from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Insert data耗时:" + durableTime +"s");

            okCnt++;
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlInsrDef.toString():  **********    " + sqlInsrDef.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }
            throw new RuntimeException(e);
        }

        //Insert之后是否成功都将redis存储的对应的数据删除，后面对比数据之后再进行插入
        if(etlConfDModel.getPanelList() != null && etlConfDModel.getPanelList().size() > 0) {
            final JedisPool jedisPool = SpringContext.getBean(JedisPool.class);
            try (Jedis jedis = jedisPool.getResource()) {
                jedis.hdel("reload_panel", etlConfDModel.getOpe() + etlConfDModel.getPanelList());
            } catch (Exception e) {
                logger.error(LoggerUtil.stackTraceToString(e));
                logger.error("************   Redis Insert Error  **********    " + etlConfDModel.getOpe() + etlConfDModel.getPanelList());
            }
        }
    }

    //查寻etl_conf_d表的his job当中的ext_2值
    public List<EtlConfDDefect> getHisJobFlag(EtlConfD etlConfD) {
        List<EtlConfDDefect> etlConfDList = new ArrayList<EtlConfDDefect>();
        QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();

        String jobName = etlConfD.getJobName();
        String hisJobFlagSql = "select ext_2 from ch_qms.etl_conf_d" +
                " where job_group_name = 'RPT_ETL'" +
                " and job_name = '" + jobName.substring(0, jobName.length() - 2) + "_His" + jobName.substring(jobName.length() - 2) + "'";

        try {
            etlConfDList = qr_mysql.query(hisJobFlagSql, new BeanListHandler<EtlConfDDefect>(EtlConfDDefect.class));  //获取etl_conf_d表中his job的ext_2，作为current job的run job的参考
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }

        }

        return etlConfDList;
    }

    //查寻etl_conf_d表的current job当中的ext_2值
    public List<EtlConfDDefect> getCurJob(EtlConfD etlConfD) {
        List<EtlConfDDefect> etlConfDList = new ArrayList<EtlConfDDefect>();
        QueryRunner qr_mysql = JdbcUtil.getQueryRunner_mysql();

        String jobName = etlConfD.getJobName();
        String hisJobFlagSql = "select RUN_END_TIMESTAMP,BUFFER_MINUTE,QUERY_MINUTE,EXT_2 from ch_qms.etl_conf_d" +
                " where job_group_name = 'RPT_ETL'" +
                " and job_name = '" + jobName.substring(0, jobName.length() - 6) + jobName.substring(jobName.length() - 2) + "'";

        try {
            etlConfDList = qr_mysql.query(hisJobFlagSql, new BeanListHandler<EtlConfDDefect>(EtlConfDDefect.class));  //获取etl_conf_d表中his job的ext_2，作为current job的run job的参考
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR get etlHisJob data!" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }

        }

        return etlConfDList;
    }

    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfD(EtlConfD etlConfD) {
        dao.update(etlConfD);
    }

    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfDModel(EtlConfDModel etlConfDModel) {
        dao.update(etlConfDModel);
    }

    //获取时间段内panel的最小时间，作为delete Panel Summary表中的起始时间
    public List<YearMonth> getYearMonth(EtlConfDModel etlConfDModel, String tmpTab) {
        List<YearMonth> yearMonthList = new ArrayList<YearMonth>();
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        String sqlBuffDef = "";

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
            opeList.add("L4110");
            opeList.add("L4212");
            opeList.add("CT1");
            opeList.add("CT1F");
        }

        if (opeList.contains(etlConfDModel.getOpe())) {
            //获取源数据汇总panel_qty
            sqlBuffDef = "select glass_id, min(substring(glass_id,2,2)) as year, min(substring(glass_id,4,1)) as month" +
                    "  from " + tmpTab.substring(0, 12) + "glass_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef = sqlBuffDef + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffDef = sqlBuffDef + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffDef = sqlBuffDef + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
                    " and glass_id not like '%*%'";
        } else {
            //获取源数据汇总panel_qty
            sqlBuffDef = "select min(substring(glass_id,2,2)) as year, min(substring(glass_id,4,1)) as month" +
                    "  from " + tmpTab.substring(0, 12) + "panel_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sqlBuffDef = sqlBuffDef + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sqlBuffDef = sqlBuffDef + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sqlBuffDef = sqlBuffDef + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'" +
                    " and substring(glass_id,1,1) in ('A','C')" +
                    " and glass_id not like '%*%'" +
                    " group by glass_id" +
                    " order by year, month";
        }

        try {
            yearMonthList = qr_gp.query(sqlBuffDef, new BeanListHandler<YearMonth>(YearMonth.class));  //获取panel的最小时间
            logger.info("get panel min glass_id time " + "from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }

        return yearMonthList;
    }

    public List<MinMaxEvttimeStamp> getMinMaxEvtTime(EtlConfDModel etlConfDModel, String tmpTab) {
        List<MinMaxEvttimeStamp> minMaxEvtTime = new ArrayList<MinMaxEvttimeStamp>();
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        String sql = "";

        List<String> opeList = new ArrayList<String>();
        if ("OC".equals(etlConfDModel.getShop())) {
            opeList.add("4600");
            opeList.add("4610");
            opeList.add("466X");
            opeList.add("4670");
            opeList.add("5600");
            opeList.add("6621");
            opeList.add("6622");
            opeList.add("6641");
            opeList.add("7200");
            opeList.add("7210");
            opeList.add("7220");
            opeList.add("L4110");
            opeList.add("L4212");
            opeList.add("CT1");
            opeList.add("CT1F");
        }

        if (opeList.contains(etlConfDModel.getOpe())) {
            sql = "select max( evt_timestamp ) maxTime, min( evt_timestamp ) minTime" +
                    "  from " + tmpTab.substring(0, 12) + "glass_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sql = sql + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sql = sql + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sql = sql + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'";
        } else {
            sql = "select max( evt_timestamp ) maxTime, min( evt_timestamp ) minTime" +
                    "  from " + tmpTab.substring(0, 12) + "panel_f";

            //若标记ext_1栏位值为Y，则按evt_timestamp进行数据汇总，反之，平时按照db_timestamp进行数据汇总（evt_timestamp在起始时间的一个月之内）
            if ("Y".equals(etlConfDModel.getExt1().toString())) {
                sql = sql + " where evt_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and evt_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            } else {
                sql = sql + " where db_timestamp >= '" + etlConfDModel.getRunStartTimestamp() + "'" +
                        " and db_timestamp < '" + etlConfDModel.getRunEndTimestamp() + "'";
            }

            sql = sql + " and ope_id = '" + etlConfDModel.getOpe() + "'" +
                    " and real_flg = 'Y'";
        }

        try {
            minMaxEvtTime = qr_gp.query(sql, new BeanListHandler<MinMaxEvttimeStamp>(MinMaxEvttimeStamp.class));  //获取panel的最小时间
            logger.info("get panel min evt_timestamp " + "from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            try {
                throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
            } catch (ETLException e1) {
                logger.error("报错信息：" + LoggerUtil.stackTraceToString(e1));
                e1.printStackTrace();
            }
        }

        return minMaxEvtTime;
    }
}
